# coding: utf-8

# NOTE by zhouhl 2018-03-28
# 修复报单系统中，因为客服选择“无效(不创建)”，而导致的 DemandClient 中 demand_id 为 0 的问题。
# 理论上官网留电的客户会有一个草稿/未审核的项目，SEM需要此项目中的sem信息进行优化


import os
import set_env_path     # 请勿删除

from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def fix_invaled_demand_clents():
    find_need_fix_demand_clients_sql = text("""
        select
            user_id,max(id) as id
        from
            demand_clients
        where
            operate_result=10
        group by
            user_id;
    """)

    find_max_demand_sql = text("""
        select
            id
        from
            demand
        where
            uid = :uid
            and status = 410
        order by
            id desc
        limit 1;
    """)

    fix_demand_clients_sql = text("""
        update
            demand_clients
        set
            demand_id = :demand_id
        where
            id = :id;
    """)

    find_need_fix_demand_clients = conn.execute(
        find_need_fix_demand_clients_sql
    ).fetchall()

    for i in find_need_fix_demand_clients:
        demand = conn.execute(
            find_max_demand_sql,
            uid=i.user_id
        ).first()
        if demand:
            conn.execute(
                fix_demand_clients_sql,
                demand_id=demand.id,
                id=i.id,
            )


if __name__ == '__main__':
    fix_invaled_demand_clents()
